package t;
import java.sql.*;

public class DBBean {
	private String driver="com.mysql.jdbc.Driver";
	private String url="jdbc:mysql://localhost:3306/";
	private String user="";
	private String passwd="";
	private Connection connect;
	private ResultSet rs;
	private int flag=-1;

	private String res="";

	public DBBean(String User,String passwd){
		url = url+User;
		this.user = User;
		this.passwd = passwd;
		createConnect();
	}
	
	public void createConnect(){
		try
		{
			Class.forName(this.driver).newInstance(); 
			this.connect = DriverManager.getConnection(this.url,this.user,this.passwd);
			this.flag = 1;
		}
		catch(Exception e)
		{
			System.out.println(e.getMessage());
			res = res+"初始化数据库失败！";
		}
	}
	
	public int getFlag(){
		return flag;
	}
	
	public String getRes(){
		return this.res;
	}

	public void runSql(String sql){
		this.res = "";
		try
		{
			Statement state = connect.createStatement();
			boolean hasReaSet = state.execute(sql);
			if(hasReaSet) {
				rs = state.getResultSet();
				setRes();
			}
			else res = "操作已完成";
		}
		catch(Exception e)
		{
			this.res = e.getMessage();
			System.out.println(res);
		}
	}
	public String selectNSS(String examName){
		String name="",sid="",score="";
		try{
			Statement sta = connect.createStatement();
			sta.execute("use websql");
			rs = sta.executeQuery("select name,login.sid,sum(score) from login,subject where login.sid=subject.sid and examName='"+examName+"' group by subject.sid,name");
			boolean flag = true;
			while(rs.next()){
				name = name+rs.getString(1)+"#";
				sid = sid+rs.getString(2)+"#";
				score = score+rs.getString(3)+"#";
				flag = false;
			}
			if(flag) return "";
			return (name+"@"+sid+"@"+score);
		}
		catch(Exception e){
			return "";
		}
	}
	
	public String selectSub(String examName,String sid){
		String subject="",anser="",score="";
		try{
			Statement sta = connect.createStatement();
			sta.execute("use websql");
			rs = sta.executeQuery("select subjectData,anserData,score from subject where sid='"+sid+"' and examName='"+examName+"'");
			while(rs.next()){
				subject = subject+rs.getString(1)+"#";
				anser = anser+rs.getString(2)+"#";
				score = score+rs.getInt(3)+"#";
			}
			return (subject+"@"+anser+"@"+score);
		}
		catch(Exception e){
			return e.getMessage()+"异常@异常@异常";
		}
	}
	
	public String[] selectExam(){
		String[] res = new String[2];
		String name="";
		String time = "";
		try{
			Statement sta = connect.createStatement();
			rs = sta.executeQuery("select examName,startTime from exam");
			while(rs.next()){
				name = name+rs.getString(1)+"#";
				time = time+rs.getDate(2)+"#";
			}
			res[0] = name;
			res[1] = time;
		}
		catch(Exception e){
			res[0] = e.getMessage();
		}
		return res;
	}

	public void loadData(String file,String tableName){ // file包含文件的完整绝对路径和文件名，后缀名
		String sql = "load data local infile '"+file+"' into table "+tableName+" character set utf8 fields terminated by '\t';";
		try{
			Statement sta = connect.createStatement();
			sta.execute(sql);
			
		}
		catch(Exception e){
			res = "导入数据失败！"+e.getMessage();
		}
	}
	
	private void setRes(){
		try
		{
			ResultSetMetaData d = rs.getMetaData();
			int cols = d.getColumnCount();
			for(int i=0;i<cols;i++){
				res = res+d.getColumnName(i+1)+"\t";
			}
			res+="\n\n";
			while(rs.next()){
				for(int i=0;i<cols;i++){
					res = res+rs.getString(i+1)+"\t";
				}
				res+="\n";
			}
		}
		catch(Exception e){
			res = res +"\n"+e.getMessage();
		}
		
	}

	public void destroy(){
		try
		{
			connect.close();
		}
		catch(Exception e){

		}
		
	}

}